-- 创建数据库
CREATE DATABASE TutoringSystem;
GO

USE TutoringSystem;
GO

-- 1. 用户表 - 存储学生、家长、教员三类用户信息
CREATE TABLE Users (
    user_id INT PRIMARY KEY IDENTITY(1,1),
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    real_name VARCHAR(50) NOT NULL,
    contact_info VARCHAR(100) NOT NULL,
    user_type VARCHAR(20) NOT NULL, -- 'student', 'parent', 'teacher'
    education_background TEXT,       -- 教员教育背景
    study_needs TEXT,                -- 学生学习需求
    registration_time DATETIME DEFAULT GETDATE(),
    is_active BIT DEFAULT 1
);
GO

-- 2. 课程表 - 存储课程基本信息
CREATE TABLE Courses (
    course_id INT PRIMARY KEY IDENTITY(1,1),
    course_name VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    grade_level VARCHAR(50) NOT NULL,
    course_duration INT NOT NULL, -- 单位：分钟
    price DECIMAL(10,2) NOT NULL,
    teacher_id INT NOT NULL,
    course_desc TEXT,
    create_time DATETIME DEFAULT GETDATE(),
    is_available BIT DEFAULT 1,
    
    -- 外键关联教员表
    FOREIGN KEY (teacher_id) REFERENCES Users(user_id)
);
GO

-- 3. 用户-课程关联表 - 建立学生/家长与课程的关联
CREATE TABLE User_Course (
    id INT PRIMARY KEY IDENTITY(1,1),
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    relation_type VARCHAR(20) NOT NULL, -- 'enrolled', 'interested'
    
    -- 外键约束
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    
    -- 唯一约束确保同一用户对同一课程只有一种关系
    UNIQUE (user_id, course_id, relation_type)
);
GO

-- 4. 预约表 - 存储课程预约信息
CREATE TABLE Appointments (
    appointment_id INT PRIMARY KEY IDENTITY(1,1),
    student_id INT NOT NULL,
    teacher_id INT NOT NULL,
    course_id INT NOT NULL,
    appointment_time DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- 'pending', 'confirmed', 'completed', 'cancelled'
    create_time DATETIME DEFAULT GETDATE(),
    remark TEXT,
    
    -- 外键约束
    FOREIGN KEY (student_id) REFERENCES Users(user_id),
    FOREIGN KEY (teacher_id) REFERENCES Users(user_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
GO

-- 5. 评价表 - 存储用户之间的评价
CREATE TABLE Reviews (
    review_id INT PRIMARY KEY IDENTITY(1,1),
    reviewer_id INT NOT NULL,
    reviewee_id INT NOT NULL,
    course_id INT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    review_time DATETIME DEFAULT GETDATE(),
    
    -- 外键约束
    FOREIGN KEY (reviewer_id) REFERENCES Users(user_id),
    FOREIGN KEY (reviewee_id) REFERENCES Users(user_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
GO

-- 6. 为常用查询创建索引
CREATE INDEX idx_users_user_type ON Users(user_type);
CREATE INDEX idx_courses_subject_grade ON Courses(subject, grade_level);
CREATE INDEX idx_appointments_status ON Appointments(status);
CREATE INDEX idx_reviews_reviewee ON Reviews(reviewee_id);